﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

/// <summary>
/// Summary description for ACSService
/// </summary>
public class ACSService
{
	public ACSService()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public static Boolean addNewMember(string fname,string lname,string email, string phone, string student_id, int university, string subject_area, string year, string degree_type,string career_interest, string gender, string ethnicity, Boolean uk_citizen, string age_group){
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"INSERT INTO MEMBERS
                      (FIRST_NAME, LAST_NAME, EMAIL, PHONE, UNIVERSITY_ID, STUDENT_ID, CREATED_DATE, SUBJECT_AREA, YEAR_OF_STUDY, DEGREE_TYPE, 
                      CAREER_INTEREST, GENDER, ETHNICITY, AGE_GROUP)
                        VALUES     (@fname,@lname,@email,@phone,@uni,@student_id, getUTCDate(),@sbj,@year,@degree,@career,@gender,@ethnicity,@age)";

        SqlCommand cmd = new SqlCommand(sql,conn);
        cmd.Parameters.AddWithValue("fname",fname);
        cmd.Parameters.AddWithValue("lname",lname);
        cmd.Parameters.AddWithValue("email",email);
        cmd.Parameters.AddWithValue("phone",phone);
        cmd.Parameters.AddWithValue("uni",university);
        cmd.Parameters.AddWithValue("student_id",student_id);
        cmd.Parameters.AddWithValue("sbj",subject_area);
        cmd.Parameters.AddWithValue("year",year);
        cmd.Parameters.AddWithValue("degree",degree_type);
        cmd.Parameters.AddWithValue("career",career_interest);
        cmd.Parameters.AddWithValue("gender",gender);
        //cmd.Parameters.AddWithValue("uk_citizen",null);
        cmd.Parameters.AddWithValue("age",age_group);
        cmd.Parameters.AddWithValue("ethnicity",ethnicity);

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
            return true;
        }catch(Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }

    public static Boolean addNewMember(string fname, string lname, string email, string phone, string student_id, int university, string subject_area, string year, string degree_type, string career_interest, string gender, string ethnicity, Boolean uk_citizen, string age_group, Boolean paid)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"INSERT INTO MEMBERS
                      (FIRST_NAME, LAST_NAME, EMAIL, PHONE, UNIVERSITY_ID, STUDENT_ID, CREATED_DATE, SUBJECT_AREA, YEAR_OF_STUDY, DEGREE_TYPE, 
                      CAREER_INTEREST, GENDER, ETHNICITY, AGE_GROUP, PAID)
                      VALUES (@fname,@lname,@email,@phone,@uni,@student_id, getUTCDate(),@sbj,@year,@degree,@career,@gender,@ethnicity,@age,@paid)";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("fname", fname);
        cmd.Parameters.AddWithValue("lname", lname);
        cmd.Parameters.AddWithValue("email", email);
        cmd.Parameters.AddWithValue("phone", phone);
        cmd.Parameters.AddWithValue("uni", university);
        cmd.Parameters.AddWithValue("student_id", student_id);
        cmd.Parameters.AddWithValue("sbj", subject_area);
        cmd.Parameters.AddWithValue("year", year);
        cmd.Parameters.AddWithValue("degree", degree_type);
        cmd.Parameters.AddWithValue("career", career_interest);
        cmd.Parameters.AddWithValue("gender", gender);
        //cmd.Parameters.AddWithValue("uk_citizen", null);
        cmd.Parameters.AddWithValue("age", age_group);
        cmd.Parameters.AddWithValue("ethnicity", ethnicity);
        cmd.Parameters.AddWithValue("paid", paid);

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
            return true;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }

    public static Boolean updateMember(int id, string fname, string lname, string email, string phone, string student_id, int university, string subject_area, string year, string degree_type, string career_interest, string gender, string ethnicity, Boolean uk_citizen, string age_group, Boolean paid)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"UPDATE MEMBERS SET 
                      FIRST_NAME=@fname, LAST_NAME=@lname, EMAIL=@email, PHONE=@phone, UNIVERSITY_ID=@uni, STUDENT_ID=@student_id, SUBJECT_AREA=@sbj, YEAR_OF_STUDY=@year, DEGREE_TYPE=@degree, 
                      CAREER_INTEREST=@career, GENDER=@gender, ETHNICITY=@ethnicity, AGE_GROUP=@age, PAID=@paid 
                      WHERE ID=@id";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("fname", fname);
        cmd.Parameters.AddWithValue("lname", lname);
        cmd.Parameters.AddWithValue("email", email);
        cmd.Parameters.AddWithValue("phone", phone);
        cmd.Parameters.AddWithValue("uni", university);
        cmd.Parameters.AddWithValue("student_id", student_id);
        cmd.Parameters.AddWithValue("sbj", subject_area);
        cmd.Parameters.AddWithValue("year", year);
        cmd.Parameters.AddWithValue("degree", degree_type);
        cmd.Parameters.AddWithValue("career", career_interest);
        cmd.Parameters.AddWithValue("gender", gender);
        //cmd.Parameters.AddWithValue("uk_citizen", null);
        cmd.Parameters.AddWithValue("age", age_group);
        cmd.Parameters.AddWithValue("ethnicity", ethnicity);
        cmd.Parameters.AddWithValue("paid", paid);
        cmd.Parameters.AddWithValue("id", id);

        try
        {
            conn.Open();
            int i =  cmd.ExecuteNonQuery();
            if (i > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }

    public static void deleteMember(int id)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"DELETE FROM MEMBERS WHERE ID = @ID ";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("ID", id);

        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
    }

    public static Member getMember(int id)
    {
        Member m = new Member();
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"SELECT M.*,U.* FROM MEMBERS M INNER JOIN UNIVERSITIES U ON M.UNIVERSITY_ID=U.ID WHERE M.ID = @ID ";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("ID", id);

        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        
        if (dr.Read())
        {
            m.ID = Convert.ToInt32(dr["ID"]);
            m.FirstName = dr["FIRST_NAME"].ToString();
            m.LastName = dr["LAST_NAME"].ToString();
            m.Email = dr["EMAIL"].ToString();
            m.Phone = dr["PHONE"].ToString();
            m.StudentID = dr["STUDENT_ID"].ToString();
            m.CreatedDate = DateTime.Parse(dr["CREATED_DATE"].ToString());
            m.SubjectArea = dr["SUBJECT_AREA"].ToString();
            m.YearOfStudy = dr["YEAR_OF_STUDY"].ToString();
            m.DegreeType = dr["DEGREE_TYPE"].ToString();
            m.CareerInterest = dr["CAREER_INTEREST"].ToString();
            m.Gender = dr["GENDER"].ToString();
            m.Ethnicity = dr["ETHNICITY"].ToString();
            //m.UkCitizen = Convert.ToBoolean(dr["UK_CITIZEN"].ToString());
            m.AgeGroup = dr["AGE_GROUP"].ToString();
            m.University = getUniversity(Convert.ToInt32(dr["UNIVERSITY_ID"]));
            m.Paid = Boolean.Parse(dr["PAID"].ToString());
        }
        else
        {
            m = null;
        }
        dr.Close();
        conn.Close();
        return m;

    }

    public static University getUniversity(int id)
    {
        University u = new University();
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"SELECT * FROM UNIVERSITIES WHERE ID = @ID ";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("ID", id);

        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.Read())
        {
            u.ID = Convert.ToInt32(dr["ID"]);
            u.UniversityName = dr["UNIVERISTY_NAME"].ToString();
            u.Location = dr["LOCATION"].ToString();
        }
        else
        {
            u = null;
        }
        
        return u;
    }

    public static void addUniAdmin(string user_id, int uni_id ){
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"INSERT INTO UNI_ADMIN (USER_ID,UNI_ID) VALUES (@USER,@UNI)";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("USER", user_id);
        cmd.Parameters.AddWithValue("UNI", uni_id);

       // try
        //{
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
       // }
       // catch (Exception ex)
       // {
       //     throw new Exception(ex.Message);
       // }
      //  finally
       // {
       //     conn.Close();
       // }
    }

    public static void deleteUniAdmin(string user_id)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"DELETE FROM UNI_ADMIN WHERE USER_ID=@USER";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("USER", user_id);

        // try
        //{
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
        // }
        // catch (Exception ex)
        // {
        //     throw new Exception(ex.Message);
        // }
        //  finally
        // {
        //     conn.Close();
        // }
    }

    public static Boolean IsMemberExist(string email, int uni_id)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"SELECT * FROM MEMBERS WHERE EMAIL=@EMAIL AND UNIVERSITY_ID=@UNI_ID";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("EMAIL", email);
        cmd.Parameters.AddWithValue("UNI_ID", uni_id);
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.HasRows)
        {
            dr.Close();
            conn.Close();
            return true;
           
        }
        else
        {
            dr.Close();
            conn.Close();
            return false;
        }

    }

    public static int getAdminUni(string user_id)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"SELECT * FROM UNI_ADMIN WHERE USER_ID=@UNI_ID";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("UNI_ID", user_id);
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.HasRows)
        {
            dr.Read();

            int val = Convert.ToInt32(dr["UNI_ID"].ToString());

            dr.Close();
            conn.Close();
            return val;
        }
        else
        {
            return - 1;
        }
    }

    public static String getUniName(int id)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"SELECT * FROM UNIVERSITIES WHERE ID=@UNI_ID";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("UNI_ID", id);
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.HasRows)
        {
            dr.Read();

            string val = dr["UNIVERISTY_NAME"].ToString();

            dr.Close();
            conn.Close();
            return val;
        }
        else
        {
            return null;
        }
    }


    public static int getMembersCount(int uni_id)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"SELECT count(*) as count FROM MEMBERS WHERE UNIVERSITY_ID=@UNI_ID";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("UNI_ID", uni_id);
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.HasRows)
        {
            dr.Read();

            int val = Convert.ToInt32(dr["count"].ToString());

            dr.Close();
            conn.Close();
            return val;
        }
        else
        {
            return -1;
        }
    }

    public static int getAdminRequestCount()
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"SELECT count(*) as count FROM ADMIN_REQUEST WHERE APPROVED='FALSE' ";

        SqlCommand cmd = new SqlCommand(sql, conn);
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.HasRows)
        {
            dr.Read();

            int val = Convert.ToInt32(dr["count"].ToString());

            dr.Close();
            conn.Close();
            return val;
        }
        else
        {
            return -1;
        }
    }

    public static int getMembersCount()
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"SELECT count(*) as count FROM MEMBERS";

        SqlCommand cmd = new SqlCommand(sql, conn);
        conn.Open();
        SqlDataReader dr = cmd.ExecuteReader();

        if (dr.HasRows)
        {
            dr.Read();

            int val = Convert.ToInt32(dr["count"].ToString());

            dr.Close();
            conn.Close();
            return val;
        }
        else
        {
            return -1;
        }
    }


    public static Boolean sendAdminRequest(string acs, string name, string email, string phone, int university, string position)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"INSERT INTO ADMIN_REQUEST
                      (ACS_NAME,UNI_ID,CONTACT_NAME,CONTACT_EMAIL,CONTACT_NUMBER,REQUEST_DATE,APPROVED,POSITION)
                        VALUES     (@ACS_NAME,@UNI_ID,@CONTACT_NAME,@CONTACT_EMAIL,@CONTACT_NUMBER,getDate(),'false',@POSITION)";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("CONTACT_NAME", name);
        cmd.Parameters.AddWithValue("CONTACT_EMAIL", email);
        cmd.Parameters.AddWithValue("CONTACT_NUMBER", phone);
        cmd.Parameters.AddWithValue("UNI_ID", university);
        cmd.Parameters.AddWithValue("ACS_NAME", acs);
        cmd.Parameters.AddWithValue("POSITION", position);

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
            return true;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }

    public static void updateRequestStatus(int id, Boolean status, string username)
    {

        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"UPDATE ADMIN_REQUEST SET APPROVED=@APPROVED, USERNAME=@USERNAME WHERE ID=@ID";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("APPROVED", status);
        cmd.Parameters.AddWithValue("ID", id);
        cmd.Parameters.AddWithValue("USERNAME", username);

        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            conn.Close();
        }
    }

    public static SqlDataReader getAllMembers()
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"SELECT MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME, MEMBERS.EMAIL, MEMBERS.PHONE, UNIVERSITIES.UNIVERISTY_NAME, MEMBERS.STUDENT_ID, 
                      MEMBERS.SUBJECT_AREA, MEMBERS.YEAR_OF_STUDY, MEMBERS.DEGREE_TYPE, MEMBERS.CAREER_INTEREST, MEMBERS.GENDER, MEMBERS.ETHNICITY, 
                      CASE WHEN MEMBERS.UK_CITIZEN = 1 THEN 'Yes' ELSE 'No' END AS UK_CITIZEN, MEMBERS.AGE_GROUP, CASE WHEN MEMBERS.PAID = 1 THEN 'Yes' ELSE 'No' END AS PAID, MEMBERS.CREATED_DATE
                      FROM MEMBERS INNER JOIN
                      UNIVERSITIES ON MEMBERS.UNIVERSITY_ID = UNIVERSITIES.ID";

        SqlCommand cmd = new SqlCommand(sql, conn);
        //DataSet ds = new DataSet();
        SqlDataReader dr;
        conn.Open();
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
        //conn.Close();
        return dr;
    }

    public static SqlDataReader getAllMembers(int uni_id)
    {
        SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ToString());
        string sql = @"SELECT MEMBERS.FIRST_NAME, MEMBERS.LAST_NAME, MEMBERS.EMAIL, MEMBERS.PHONE, UNIVERSITIES.UNIVERISTY_NAME, MEMBERS.STUDENT_ID, 
                      MEMBERS.SUBJECT_AREA, MEMBERS.YEAR_OF_STUDY, MEMBERS.DEGREE_TYPE, MEMBERS.CAREER_INTEREST, MEMBERS.GENDER, MEMBERS.ETHNICITY, 
                      CASE WHEN MEMBERS.UK_CITIZEN = 1 THEN 'Yes' ELSE 'No' END AS UK_CITIZEN, MEMBERS.AGE_GROUP, CASE WHEN MEMBERS.PAID = 1 THEN 'Yes' ELSE 'No' END AS PAID, MEMBERS.CREATED_DATE
                      FROM MEMBERS INNER JOIN
                      UNIVERSITIES ON MEMBERS.UNIVERSITY_ID = UNIVERSITIES.ID
                      WHERE UNIVERSITIES.ID=@UNI";

        SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.AddWithValue("UNI", uni_id);
        //DataSet ds = new DataSet();
        SqlDataReader dr;
        conn.Open();
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);        
        //conn.Close();
        return dr;
    }
}